package org.example.tool;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.excel.ExcelColumn;
import org.example.excel.ExcelRow;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.DateTimeParseException;
import java.util.*;

public class ExcelTool {

    // 必须存在的字段列表
    private static final Set<String> REQUIRED_FIELDS = new HashSet<>(Arrays.asList(
            "作物", "验标时间", "保险公司", "简称", "遥感影像时间", "出图遥感影像id", "报告时间"
    ));

    /**
     * 判断Excel文件中是否存在所需的必填字段
     *
     * @param filePath Excel文件路径
     * @return true 如果存在全部必填字段，否则 false
     */
    public static boolean hasRequiredFields(String filePath) {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 假设数据在第一个sheet
            Row headerRow = sheet.getRow(0); // 第一行为表头

            if (headerRow == null) {
                return false; // 没有表头行
            }

            Set<String> headers = new HashSet<>();
            for (Cell cell : headerRow) {
                String value = getCellValueAsString(cell).trim();
                headers.add(value);
            }

            // 检查是否包含所有必须字段
            return headers.containsAll(REQUIRED_FIELDS);

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static List<ExcelRow> readExcel(String filePath) throws IOException, IllegalAccessException, InstantiationException {
        return readExcel(filePath, ExcelRow.class,0);

    }
    /**
     * 将单元格值转换为字符串形式
     *
     * @param cell 单元格对象
     * @return 单元格内容的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
    public static <T> List<T> readExcel(String filePath, Class<T> clazz,int headRow) throws IOException, IllegalAccessException, InstantiationException {
        List<T> dataList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(headRow);
            Map<String, Integer> headerMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                headerMap.put(cell.getStringCellValue(), i);
            }

            for (int i = headRow+1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                T obj = clazz.newInstance();
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    if (field.isAnnotationPresent(ExcelColumn.class)) {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        String columnName = annotation.value();
                        if (headerMap.containsKey(columnName)) {
                            int columnIndex = headerMap.get(columnName);
                            Cell cell = row.getCell(columnIndex);
                            if (cell != null) {
                                field.setAccessible(true);
                                if (field.getType() == String.class) {
                                    field.set(obj, getCellValueAsString(cell));
                                } else if (field.getType() == Integer.class) {
                                    field.set(obj, getCellValueAsInt(cell));
                                }
                                else if (field.getType() == BigDecimal.class) {
                                    field.set(obj, getCellValueAsBigDecimal(cell));
                                }
                                else if (field.getType() == LocalDate.class) {
                                    field.set(obj, getCellValueAsLocalDate(cell));

                                }
                                else if (field.getType() == Short.class) {
                                    // 解析字符串为 LocalDate 对象
                                    field.set(obj, getCellValueAsShort(cell));
                                }
                            }
                        }
                    }
                }
                dataList.add(obj);
            }
        }
        return dataList;
    }
    private static Integer getCellValueAsInt(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return (int) cell.getNumericCellValue();
            case STRING:
                try {
                    return Integer.parseInt(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    private static Short getCellValueAsShort(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return (short) cell.getNumericCellValue();
            case STRING:
                try {
                    return Short.valueOf(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    private static BigDecimal getCellValueAsBigDecimal(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return BigDecimal.valueOf(cell.getNumericCellValue());
            case STRING:
                try {
                    return  new BigDecimal(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    private static LocalDate getCellValueAsLocalDate(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                try {
                    Date date = cell.getDateCellValue();
                    java.time.Instant instant = date.toInstant();
                    // 获取系统默认时区
                    java.time.ZoneId zoneId = ZoneId.systemDefault();
                    // 将 Instant 结合时区信息转换为 ZonedDateTime
                    java.time.ZonedDateTime zonedDateTime = instant.atZone(zoneId);
                    // 从 ZonedDateTime 中提取 LocalDate
                    return zonedDateTime.toLocalDate();
                } catch (NumberFormatException e) {
                    return null;
                }
            case STRING:
                DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                        .appendOptional(DateTimeFormatter.ofPattern("yyyy-MM-dd"))
                        .appendOptional(DateTimeFormatter.ofPattern("yyyy-M-d"))
                        .toFormatter();
                try {
                    return LocalDate.parse(cell.getStringCellValue(), formatter);
                } catch (DateTimeParseException e) {
                    return null;
                }
            default:
                return null;
        }
    }
    public static void main(String[] args) {
        String filePath = "E:\\测试数据制图\\统计表信息.xlsx";
        System.out.println(hasRequiredFields(filePath));
    }
}
